using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace StudentManagementApplication.DAO
{
    public class StudentDAO : BaseDAO
    {
        public StudentDAO() 
        {
        }

        public Entities.Student CreateStudentFromReader(IDataReader reader)
        {
            Entities.Student student = new Entities.Student();
            student.StudentID = (string)reader["cStudentID"];
            student.ClassID = (string)reader["cClassID"];
            student.StatusOfStudying = (int)reader["iStatusOfStudying"];
            return student;
        }

        public void InsertStudent(Entities.Student student)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcInsertStudent", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", student.StudentID));
                command.Parameters.Add(new SqlParameter("@cClassID", student.ClassID));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)

            }
        }

        public void UpdateStudent(Entities.Student student)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcUpdateStudent", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", student.StudentID));
                command.Parameters.Add(new SqlParameter("@cClassID", student.ClassID));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public void DeleteStudent(string sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeleteStudent", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", sStudentID));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public List<Entities.Student> GetAllStudents()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllStudents", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                List<Entities.Student> studentList = new List<Entities.Student>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        studentList.Add(CreateStudentFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return studentList;
            }
        }

        public Entities.Student GetStudentByStudentID(string sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetStudentbyStudentID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", sStudentID));
                connection.Open();
                Entities.Student student = new Entities.Student();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        student = CreateStudentFromReader(reader);
                    }
                    //else
                    //    throw;
                }
                return student;
            }
        }

        public List<Entities.Student> GetStudentsByClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetStudentsByClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                List<Entities.Student> studentList = new List<Entities.Student>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        studentList.Add(CreateStudentFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return studentList;
            }
        }

        
    }
}
